package com.geostar.constructionland.analyse.mapper.statistics;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.geostar.constructionland.analyse.entity.statistics.SticDlfxTzh;
import com.geostar.constructionland.common.entity.biz.PieceLandInfo;
import com.geostar.constructionland.common.entity.response.LandInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * @author yelll
 * @since 2020-6-11 15:34
 **/
public interface SticDlfxTzhMapper extends BaseMapper<SticDlfxTzh> {
    /***
     * 获取调整后地类分析数据
     * @param fxbs
     * @return
     */
    List<SticDlfxTzh> getSticDlFxTzh(@Param("fxbs")String fxbs);

    /**
     * 保存调整后更低质量分析统计
     * @param fxbs
     */
    void saveGdZlFxTj(@Param("fxbs") String fxbs);
    /**
     * 获取占压面积总值
     * @param fxbs
     * @return
     */
    @Select("select sum(zymj_ys) zymjys from jsyd_fx_dlfx_tj_tzh where fxbs=#{fxbs,jdbcType=VARCHAR}")
    double getTotalZymjScaled(@Param("fxbs")String fxbs);

    /***
     * 获取该那卷下的DKMJ_YS总和
     * @param ywid
     * @return
     */
    @Select("select sum(dkmj_ys) dkmj from jsyd_dkxx where kcdjid in (select kcdjid from jsyd_xmdkgx where ywid=#{ywid,jdbcType=VARCHAR})")
    @ResultType(Double.class)
    double getTotalDkmjYs(@Param("ywid")String ywid);

    @Select("select nvl(sum(zymj_ys),0) zymjys from jsyd_fx_dlfx_tj_tzh where fxbs=#{fxbs,jdbcType=VARCHAR} and ktzmj>0")
    double getKtzZymj(@Param("fxbs")String fxbs);


    @Select(" SELECT fxid,zymj,zymj_ys FROM ( SELECT fxid,zymj,zymj_ys,  dlbm,  " +
            " CASE WHEN dlbm = '011'   THEN 10  WHEN dlbm IN (  '012',  '013'  ) THEN 20  " +
            " WHEN ktzmj = 1      THEN 30      " +
            " WHEN dlbm1 = '1'    THEN 40  " +
            " ELSE 50  END pcxh " +
            " FROM jsyd_fx_dlfx_tj_tzh a LEFT JOIN jsyd_dlbm b ON a.dlbm = b.dlbm3 WHERE fxbs = #{fxbs,jdbcType=VARCHAR} ) " +
            " ORDER BY pcxh desc, zymj_ys DESC, trunc( zymj_ys, 1 ) - round( zymj_ys, 0 )" )
    List<SticDlfxTzh> getRestAdjustOriDlFxTzh(@Param("fxbs")String fxbs);


    @Select(" select sjlx,dlid,#{fxbs} fxbs,qsxz,dkbs,zdxxid,bphxfxid,'131' dlbm," +
            " 1 ktzmj,zymj,zymj_ys as zymj_ys,dllx,sys_guid() fxid from  jsyd_fx_dlfx_tj_tzh " +
            " where fxbs=#{fxbs,jdbcType=VARCHAR}  and ktzmj=2 ")
    List<SticDlfxTzh> getKtzSticDlfxTzh(@Param("fxbs")String fxbs);



    @Select(" select sum(zymj) zymj from jsyd_fx_dlfx_tj_tzh where fxbs=#{fxbs,jdbcType=VARCHAR} and dkbs=#{dkbs,jdbcType=VARCHAR} and ktzmj=0 ")
    double getAdjustedTotalMjByDkxx(@Param("fxbs")String fxbs,@Param("dkbs")String dkbs);



    @Select("  select aaa.*,bbb.gdhj_ys,bbb.gdhj_sswr from " +
            "            (" +
            "       select aa.dlid,tbdlmj dlmj_ys,sum(zymj) dlmj_pch from " +
            "        ( " +
            "       select dlid,dlmj,tbdlmj from jsyd_fx_dlfx where fxbs = #{fxbs,jdbcType=VARCHAR} and (dlbm in ('011','012','013')  or ktzmj=1)" +
            " ) aa " +
            "   left join " +
            "   ( " +
            "     select dlid,zymj from jsyd_fx_dlfx_tj_tzh where fxbs = #{fxbs,jdbcType=VARCHAR} and ktzmj=0 and sjlx='dltb' ) " +
            "      bb on aa.dlid=bb.dlid group by aa.dlid,tbdlmj " +
            "   ) aaa " +
            " left join " +
            " (" +
            " select    dlid, sum(tbdlmj) gdhj_ys ,sum(round(tbdlmj,0)) gdhj_sswr from jsyd_fx_gdzlfx where fxbs=#{fxbs,jdbcType=VARCHAR} group by dlid" +
            " ) bbb " +
            " on aaa.dlid=bbb.dlid ")
    List<Map> getGdInfo(@Param("fxbs") String fxbs);


    @Select({"<script> ",
            " select aa.fxid,aa.zymj_ys,aa.zymj from jsyd_fx_dlfx_tj_tzh aa left join jsyd_dlbm bb on aa.dlbm=bb.dlbm3 " ,
            " where fxbs=#{fxbs,jdbcType=VARCHAR} and dlbm = '011' order by case when pcxh is null then 0 else pcxh end,zymj_ys desc,trunc(zymj_ys,1)-round(zymj_ys,0) ",
            " <when test='sortType!=null'>",
            " ${sortType}",
            " </when>",
            " </script>"})
    List<SticDlfxTzh> getSortedPdyFields(@Param("fxbs")String fxbs , @Param("sortType") String sortType);

    @Select({"<script> ",
            " select aa.fxid,aa.zymj_ys,aa.zymj from jsyd_fx_dlfx_tj_tzh aa left join jsyd_dlbm bb on aa.dlbm=bb.dlbm3 " ,
            " where fxbs=#{fxbs,jdbcType=VARCHAR} and dlbm in ('012','013') order by case when pcxh is null then 0 else pcxh end,zymj_ys desc,trunc(zymj_ys,1)-round(zymj_ys,0) ",
            " <when test='sortType!=null'>",
            " ${sortType}",
            " </when>",
            " </script>"})
    List<SticDlfxTzh> getSortedDryIrgFields(@Param("fxbs")String fxbs , @Param("sortType") String sortType);


    @Select({"<script> ",
            "  select aa.fxid,aa.zymj_ys,aa.zymj from jsyd_fx_dlfx_tj_tzh aa left join jsyd_dlbm bb on aa.dlbm=bb.dlbm3" +
                    "  where fxbs=#{fxbs,jdbcType=VARCHAR} and ktzmj>0 order by " +
                    "  case when pcxh is null then 0 else pcxh end," +
                    "  zymj_ys desc,trunc(zymj_ys,1)-round(zymj_ys,0)",
            " <when test='sortType!=null'>",
            " ${sortType}",
            " </when>",
            " </script>"})
    List<SticDlfxTzh> getSortedKtzFields(@Param("fxbs")String fxbs,@Param("sortType")String sortType);


    @Select({"<script> ",
            "  select a.fxid, a.zymj_ys, a.zymj from jsyd_fx_dlfx_tj_tzh a left join jsyd_dlbm b on a.dlbm = b.dlbm3 " +
            " where  a.fxbs=#{fxbs,jdbcType=VARCHAR} and b.dlbm1='1' ORDER BY zymj_ys DESC, trunc( zymj_ys, 1 ) - round( zymj_ys, 0 )  ",
            " <when test='sortType!=null'>",
            " ${sortType}",
            " </when>",
            " </script>"})
    List<SticDlfxTzh> getSortedBuildFields(@Param("fxbs")String fxbs,@Param("sortType")String sortType);


    @Select({"<script> ",
            " select fxid,zymj,zymj_ys,zymj-round(zymj_ys,0) sub,bphxfxid from " +
            " (" +
            "    select case when a.dlbm='011' then 10 " +
            "    when a.dlbm in ('012','013') then 20 " +
            "    when ktzmj=2 then 30" +
            "    when b.dlbm1='1' then 40 else 50 end ss," +
            "    a.fxid,a.zymj,a.zymj_ys,zymj-round(zymj_ys,0) sub," +
            "    dlbm,ktzmj,a.bphxfxid,sjlx " +
            "    from jsyd_fx_dlfx_tj_tzh a left join jsyd_dlbm b on a.dlbm = b.dlbm3 " +
            "    where fxbs=#{fxbs,jdbcType=VARCHAR}" +
            " ) " +
            " where ss=50  and round(zymj_ys,1)>=0.2  " +
            " order by zymj_ys desc, bphxfxid desc,dlbm,sjlx,trunc(zymj_ys,1)-round(zymj_ys,0) " +
            " <when test='sortType!=null'>" ,
            "  ${sortType} " ,
            " </when>",
            "</script>"})
    List<SticDlfxTzh> getSortedRestFiedls(@Param("fxbs")String fxbs,@Param("sortType")String sortType);

    @Update({" <script> " +
            " <foreach collection=\"landfInfos\" item=\"landInfo\" open=\"begin\" close=\";end;\" separator=\";\"> " +
            "  update " +
            "  jsyd_fx_dlfx_tj_tzh" +
            "  SET " +
            "   zymj = #{landInfo.zymj_adjusted,jdbcType=NUMERIC}  " +
            "  WHERE " +
            "   fxid = #{landInfo.fxId,jdbcType=VARCHAR} " +
            " </foreach> " +
            " </script> "})
    void updateSticDlfxTzh(@Param("landfInfos")List<LandInfo> landInfos);


    @Select(" SELECT " +
            " fxid,fxbs,qsxz,dkbs,zdxxid,dlbm,dlid,sjlx,ktzmj,zymj,zymj_ys,dllx,bphxfxid,t.dlbm,b.dlbm1 " +
            " FROM jsyd_fx_dlfx_tj_tzh  t  " +
            " inner join jsyd_dlbm b on t.dlbm = b.dlbm3 " +
            " where (dlbm in ('011','012','013') or ktzmj > 0 or dlbm1 = '1') and fxbs = #{fxbs,jdbcType=VARCHAR}  ")
    List<SticDlfxTzh> getKtzFarmBuildList(@Param("fxbs")String fxbs);



    @Update( " update jsyd_fx_dlfx_tj_tzh t "+
            " set t.ktzmj = '0' "+
            " where t.fxbs = #{fxbs} and t.ktzmj = '2' ")
    void updateSticKtzmjAdjust(@Param("fxbs")String fxbs);
}
